
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 06/03/2012 12:02:18
-- Generated from EDMX file: C:\SERN.src\Sern.Abstracts\Sern.ServiceImplementation\TrackerDefinition\Data\TrackerEntity.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [Sern.Tracker];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_Client_Id_Client_ClientHostId]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Client] DROP CONSTRAINT [FK_Client_Id_Client_ClientHostId];
GO
IF OBJECT_ID(N'[dbo].[FK_ClientStatusInfo_Client]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ClientStatusInfo] DROP CONSTRAINT [FK_ClientStatusInfo_Client];
GO
IF OBJECT_ID(N'[dbo].[FK_JobDescription_Job]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[JobDescription] DROP CONSTRAINT [FK_JobDescription_Job];
GO
IF OBJECT_ID(N'[dbo].[FK_JobKeyword_Job]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[JobKeyword] DROP CONSTRAINT [FK_JobKeyword_Job];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Client]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Client];
GO
IF OBJECT_ID(N'[dbo].[ClientStatusInfo]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ClientStatusInfo];
GO
IF OBJECT_ID(N'[dbo].[Job]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Job];
GO
IF OBJECT_ID(N'[dbo].[JobDescription]', 'U') IS NOT NULL
    DROP TABLE [dbo].[JobDescription];
GO
IF OBJECT_ID(N'[dbo].[JobKeyword]', 'U') IS NOT NULL
    DROP TABLE [dbo].[JobKeyword];
GO
IF OBJECT_ID(N'[dbo].[KeyAtClientLog]', 'U') IS NOT NULL
    DROP TABLE [dbo].[KeyAtClientLog];
GO
IF OBJECT_ID(N'[dbo].[KeyTree]', 'U') IS NOT NULL
    DROP TABLE [dbo].[KeyTree];
GO
IF OBJECT_ID(N'[dbo].[sysdiagrams]', 'U') IS NOT NULL
    DROP TABLE [dbo].[sysdiagrams];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Clients'
CREATE TABLE [dbo].[Clients] (
    [Id] uniqueidentifier  NOT NULL,
    [Uri] varchar(512)  NOT NULL,
    [KeyShape] varchar(256)  NULL,
    [ValueShape] varchar(256)  NULL,
    [Type] int  NOT NULL,
    [JobId] uniqueidentifier  NOT NULL,
    [ClientClusterId] bigint IDENTITY(1,1) NOT NULL,
    [ClientHostId] uniqueidentifier  NULL
);
GO

-- Creating table 'Jobs'
CREATE TABLE [dbo].[Jobs] (
    [Id] uniqueidentifier  NOT NULL,
    [StartTme] datetime  NOT NULL,
    [EndTime] datetime  NULL,
    [TrackerUri] varchar(256)  NOT NULL,
    [JobClusterId] bigint IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'KeyAtClientLogs'
CREATE TABLE [dbo].[KeyAtClientLogs] (
    [Id] uniqueidentifier  NOT NULL,
    [ClientId] uniqueidentifier  NOT NULL,
    [TimeStamp] datetime  NOT NULL,
    [KeyId] uniqueidentifier  NOT NULL,
    [State] int  NOT NULL,
    [KeyAtClientLogClusterId] bigint IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'KeyTrees'
CREATE TABLE [dbo].[KeyTrees] (
    [Id] uniqueidentifier  NOT NULL,
    [ParentKey] uniqueidentifier  NULL,
    [KeyTreeClusterId] bigint IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'sysdiagrams'
CREATE TABLE [dbo].[sysdiagrams] (
    [name] nvarchar(128)  NOT NULL,
    [principal_id] int  NOT NULL,
    [diagram_id] int IDENTITY(1,1) NOT NULL,
    [version] int  NULL,
    [definition] varbinary(max)  NULL
);
GO

-- Creating table 'JobKeywords'
CREATE TABLE [dbo].[JobKeywords] (
    [Id] uniqueidentifier  NOT NULL,
    [JobId] uniqueidentifier  NOT NULL,
    [Word] nvarchar(256)  NOT NULL,
    [JobKeywordClusterId] bigint IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'Jobs_JobDescription'
CREATE TABLE [dbo].[Jobs_JobDescription] (
    [Description] varchar(max)  NOT NULL,
    [JobDescriptionClusterId] bigint IDENTITY(1,1) NOT NULL,
    [Id] uniqueidentifier  NOT NULL
);
GO

-- Creating table 'Clients_ClientStatusInfo'
CREATE TABLE [dbo].[Clients_ClientStatusInfo] (
    [StartOfSubscription] datetime  NOT NULL,
    [LastAlivePing] datetime  NULL,
    [EndOfSubscription] datetime  NULL,
    [ClientStatusClusterId] bigint  NOT NULL,
    [Id] uniqueidentifier  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Clients'
ALTER TABLE [dbo].[Clients]
ADD CONSTRAINT [PK_Clients]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Jobs'
ALTER TABLE [dbo].[Jobs]
ADD CONSTRAINT [PK_Jobs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'KeyAtClientLogs'
ALTER TABLE [dbo].[KeyAtClientLogs]
ADD CONSTRAINT [PK_KeyAtClientLogs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'KeyTrees'
ALTER TABLE [dbo].[KeyTrees]
ADD CONSTRAINT [PK_KeyTrees]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [diagram_id] in table 'sysdiagrams'
ALTER TABLE [dbo].[sysdiagrams]
ADD CONSTRAINT [PK_sysdiagrams]
    PRIMARY KEY CLUSTERED ([diagram_id] ASC);
GO

-- Creating primary key on [Id] in table 'JobKeywords'
ALTER TABLE [dbo].[JobKeywords]
ADD CONSTRAINT [PK_JobKeywords]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Jobs_JobDescription'
ALTER TABLE [dbo].[Jobs_JobDescription]
ADD CONSTRAINT [PK_Jobs_JobDescription]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Clients_ClientStatusInfo'
ALTER TABLE [dbo].[Clients_ClientStatusInfo]
ADD CONSTRAINT [PK_Clients_ClientStatusInfo]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [JobId] in table 'JobKeywords'
ALTER TABLE [dbo].[JobKeywords]
ADD CONSTRAINT [FK_JobKeyword_Job]
    FOREIGN KEY ([JobId])
    REFERENCES [dbo].[Jobs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_JobKeyword_Job'
CREATE INDEX [IX_FK_JobKeyword_Job]
ON [dbo].[JobKeywords]
    ([JobId]);
GO

-- Creating foreign key on [ClientHostId] in table 'Clients'
ALTER TABLE [dbo].[Clients]
ADD CONSTRAINT [FK_Client_Id_Client_ClientHostId]
    FOREIGN KEY ([ClientHostId])
    REFERENCES [dbo].[Clients]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_Client_Id_Client_ClientHostId'
CREATE INDEX [IX_FK_Client_Id_Client_ClientHostId]
ON [dbo].[Clients]
    ([ClientHostId]);
GO

-- Creating foreign key on [Id] in table 'Jobs_JobDescription'
ALTER TABLE [dbo].[Jobs_JobDescription]
ADD CONSTRAINT [FK_JobDescription_inherits_Job]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[Jobs]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [Id] in table 'Clients_ClientStatusInfo'
ALTER TABLE [dbo].[Clients_ClientStatusInfo]
ADD CONSTRAINT [FK_ClientStatusInfo_inherits_Client]
    FOREIGN KEY ([Id])
    REFERENCES [dbo].[Clients]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------